#!/usr/bin/env python
# coding=utf8
import time
import pymysql
import settings
from pyzabbix import ZabbixAPI
from xlwt import *





config = {
  "host": settings.DB_HOST,
  "user": settings.DB_USER,
  "password": settings.DB_PASS,
  "db": "zabbix"
}

# from zabbix include/defines.inc.php
EVENT_SOURCE_TRIGGERS = 0

# from zabbix include/defines.inc.php
EVENT_OBJECT_TRIGGER = 0

# from zabbix include/defines.inc.php
TRIGGER_VALUE_FALSE = 0
TRIGGER_VALUE_TRUE = 1


def get_today_timestamp_tuple():
    """ get today's 0:00 timestamp and next's day 0:00  """
    now = int(time.time())
    now = now - now % (24 * 3600) - settings.TIME_DELTA
    return now, now + 3600 * 24

def get_trigger_by_triggerid(triggerid):
    zapi = ZabbixAPI(settings.ZABBIX_URL)
    zapi.login(settings.ZABBIX_USER, settings.ZABBIX_PASS)
    trigger_options = {
        "triggerids": triggerid,
        "output": ["triggerid", "description", "expression", "value"],
        "expandDescription": 1,
        "monitored": 1,
        "selectHosts": ["name"],
    }
    trigger = zapi.trigger.get(**trigger_options)[0]
    return trigger 


def get_triggers():
    zapi = ZabbixAPI(settings.ZABBIX_URL)
    zapi.login(settings.ZABBIX_USER, settings.ZABBIX_PASS)
    trigger_options = {
        "output": ["triggerid", "description", "expression", "value"],
        "expandDescription": 1,
        "monitored": 1,
        "selectHosts": ["name"],
    }
    triggers = zapi.trigger.get(**trigger_options)
    #for trigger in triggers:
    #    print trigger["hosts"][0]["name"], trigger["description"]
    return triggers


# from zabbix include/triggers.inc.php
def calculate_availability(trigger_id, start_time=0, end_time=0):
    start_value = TRIGGER_VALUE_FALSE
    _min = None
    ret = {}
    connection = pymysql.connect(**config)

    data = {
        "trigger_id": trigger_id,
        "event_source_triggers": EVENT_SOURCE_TRIGGERS,
        "event_object_trigger": EVENT_OBJECT_TRIGGER,
        "start_time": start_time
    }

    if start_time > 0 and start_time <= time.time():
        sql = "SELECT e.eventid, e.value FROM events e WHERE e.objectid={trigger_id}"\
              " AND e.source={event_source_triggers}"\
              " AND e.object={event_object_trigger}"\
              " AND e.clock < {start_time}"\
              " ORDER BY e.eventid DESC".format(**data)

        with connection.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchone()
        if result:
            start_value = result[1]
            _min = start_time

    sql = "SELECT COUNT(e.eventid) AS cnt,MIN(e.clock) AS min_clock,MAX(e.clock)"\
          " as max_clock FROM events e WHERE e.objectid={trigger_id}"\
          " AND e.source={event_source_triggers}"\
          " AND e.object={event_object_trigger}"
    
    if start_time > 0:
        sql += " AND e.clock>={start_time}"

    if end_time > 0:
        sql += " AND e.clock<={end_time}"
        data["end_time"] = end_time

    with connection.cursor() as cursor:
        cursor.execute(sql.format(**data))
        events = cursor.fetchone()
        cnt, min_clock, max_clock = events[0], events[1], events[2]

    if cnt > 0:
        if _min == None:
            _min = min_clock
        _max = max_clock
    else:
        ret["true_time"] = 0
        ret["false_time"] = 0
        ret["true"] = 100 if TRIGGER_VALUE_TRUE == start_value else 0
        ret["false"] = 100 if TRIGGER_VALUE_FALSE == start_value else 0
        return ret
    state = start_value
    true_time = 0
    false_time = 0
    _time = int(_min)
    if end_time == 0:
        end_time = _max
    rows = 0

    sql = "SELECT e.eventid, e.clock, e.value FROM events e WHERE"\
          " e.objectid={trigger_id}"\
          " AND e.source={event_source_triggers}"\
          " AND e.object={event_object_trigger}"\
          " AND e.clock BETWEEN {_min} AND {_max}"\
          " ORDER BY e.eventid"

    data["_min"] = _min
    data["_max"] = _max

    with connection.cursor() as cursor:
        cursor.execute(sql.format(**data))
        result = cursor.fetchall()
    for row in result:
        clock = int(row[1])
        value = int(row[2])
 
        diff = clock - _time
        _time = clock
        if state == 0:
            false_time += diff
            state = value
        elif state == 1:
            true_time += diff
            state = value
        rows += 1

    if rows == 0:
        trigger = get_trigger_by_triggerid(triggerid)
        state = int(trigger["value"])

    if state == TRIGGER_VALUE_FALSE:
        false_time = false_time + end_time - _time
    elif state == TRIGGER_VALUE_TRUE:
        true_time = true_time + end_time - _time

    total_time = true_time + false_time
    if total_time == 0:
        ret["true_time"] = 0
        ret["false_time"] = 0
        ret["true"] = 0
        ret["false"] = 0
    else:
        ret["true_time"] = true_time
        ret["false_time"] = false_time
        ret["true"] = ( 100.0 * true_time) / total_time
        ret["false"] = ( 100.0 * false_time) / total_time

    return ret
    

def format_rate(rate, data):
    if rate == 0 or rate == 100:
        return rate
    else:
        r = "%.2f"%(rate)
        if data < 60:
            r += "(%.2fs)"%(data)
        elif data <= 60 * 60:
            r += "(%.2fm)"%(data/60)
        else:
            r += "(%.2fh)"%(data/3600)
        return r

def main():
    data = []
    start_time, end_time = get_today_timestamp_tuple()
    triggers = get_triggers()
    for trigger in triggers:
        ret = calculate_availability(trigger["triggerid"], start_time=start_time, end_time=end_time)
        data.append({
            "host": trigger["hosts"][0]["name"],
            "description": trigger["description"],
            "true": ret["true"],
            "false": ret["false"],
            "true_time": ret["true_time"],
            "false_time": ret["false_time"]
        })
    data = sorted(data, key=lambda x: x["true"], reverse=True)
    w = Workbook()
    ws = w.add_sheet(u"可用率统计")
    ws.write(0, 0, u"主机名")
    ws.write(0, 1, u"描述")
    ws.write(0, 2, u"问题时间占比")
    ws.write(0, 3, u"OK时间占比")

    style = easyxf('pattern: pattern solid, fore_colour light_blue;'\
                   'font: colour white, bold True;')


    for i, item in enumerate(data):
        ws.write(i + 1, 0, item["host"])
        ws.write(i + 1, 1, item["description"])
        if item["true"] > 0:
            ws.write(i + 1, 2, format_rate(item["true"], item["true_time"]), style)
        else:
            ws.write(i + 1, 2, format_rate(item["true"], item["true_time"]))
        ws.write(i + 1, 3, format_rate(item["false"], item["false_time"]))
    ws.col(1).width = 12000
    ws.col(2).width = 4000
    w.save(u"{0}统计.xls".format(time.strftime("%Y%m%d", time.localtime(start_time)))) 

if __name__ == "__main__":
    main()

